<?php
class UsersModel extends SBKH{
	public function loginValidate($data = array()){
		$sql = "
			SELECT `id`, `username`, `fullname`, `email_address`, `group_id`
			FROM " . $this -> GetPrefix('users') . " 
			WHERE `email_address` = '" . escStr($data['txt-username']) . "'
				AND `password` = '" . b64encode($data['txt-password']) . "'
				AND `status` = 1
		";
		$result = $this->query($sql);
		return $result;
	}
	function logout($user_log_id = 0){
		$sql = "
			UPDATE `user_log`
			SET `logout` = '" . date('Y-m-d H:i:s') . "'
			WHERE `id` ='" . $user_log_id . "'
				";
		return $this->query($sql);
	}
	function getUsers($userid = 0,$search = ""){
		$sql = "
		SELECT u.`id`, u.`fullname`, u.`email_address`, u.`password`, u.`gender`, u.`created`, u.`created_by`, u.`modified`, u.`modified_by`,
			CASE u.`status`
				WHEN '1' THEN 'Active'
				WHEN '0' THEN 'Suspend'
				WHEN '2' THEN 'Pending Auth'
				END as status, u.`status` as status_value,
			ug.`id` as group_id, ug.`description`
		FROM " . $this -> GetPrefix('users') . " as u
				INNER JOIN " . $this -> GetPrefix('users_group') . " as ug ON u.`group_id` = ug.`id`
		";
		if($userid != 0 && $search == ""){
			$sql .= " WHERE u.`id` = '" . $userid . "'";
		}elseif($userid == 0 && $search != ""){
			$sql .= " WHERE `email_address` LIKE '%" . $search . "%'";
		}elseif($userid != 0 && $search != ""){
			$sql .= " WHERE u.`id` = '" . $userid . "' && `email_address`= '%" . $search . "%'";
		}
		$result = $this->query($sql);
		return $result;
	}
	function getUserGroup(){
		$sql = "SELECT * FROM " . $this -> GetPrefix('users_group') . " ORDER BY `description`";
		$result = $this -> Query($sql);
		return $result;
	}
	function saveUserLog($user_id = 0){
		$ip_address = $_SERVER['REMOTE_ADDR'];
		$sql = "
				INSERT INTO " . $this -> GetPrefix('users_logs') . " 
				SET `user_id` = $user_id, 
				`ip_address` ='" . $ip_address . "', 
				`login` = '" . date('Y-m-d H:i:s') . "'
				";
		return $this->query($sql);
	}
	function saveUserLogActivities($user_log_id = 0,$activity_type = "", $action = "", $file_name = ""){	
		$sql = "
		INSERT INTO `user_log_activities` 
		SET `user_log_id` = $user_log_id, 
			`activity_type` ='" . $activity_type . "', 
			`action` = '" . $action . "', 
			`filename` = '" . @mysql_escape_string($file_name) . "', 
			`datetime` = '" . date('Y-m-d H:i:s') . "'
				";		
		return $this->query($sql);
	}	
	
	function getUserLog($search = "", $start = 0, $end = 15){
		
		$uSql = "UPDATE `user_log` as ul2 SET ul2.`logout` = '" . date('Y-m-d H:i:s') . "' 
				WHERE HOUR(TIMEDIFF(ul2.`login`,'" . date('Y-m-d H:i:s') . "')) >= 1 
				AND ul2.`logout` IS NULL";
		$this->query($uSql);
		
		$where = "";
		if($search != "")
			$where = " WHERE `username` LIKE '%" . $search . "%'";
		$sql = "
		SELECT u.`username`, ul.`id`, ul.`ip_address`, ul.`login`, ul.`logout`,
			CASE u.`type`
				WHEN 'CORP' THEN 'Sokha Hotels & Resorts'
				WHEN 'U-SAR' THEN 'Sokha Angkor Hotel - Upload'
				WHEN 'U-SBR' THEN 'Sokha Beach Resort - Upload'
				WHEN 'U-SCH' THEN 'Sokha Club Holte - Upload'
				WHEN 'U-THB' THEN 'Thansur Bokor Highland Resort - Upload'
				WHEN 'TRACK' THEN 'Sokha Hotel & Resorts - Tracking'
				ELSE u.`type`
				END AS type			
		FROM `user_log` as ul INNER JOIN `user` as u ON ul.`user_id` = u.`id` 
		";
		
		$sql .= $where;
		$sql .= " ORDER BY  `login` DESC LIMIT $start,$end";		
		$result = $this->query($sql);
		/* For Pagination */
		$sql = "SELECT COUNT(ul.`id`) as number_of_rows FROM `user_log` as ul INNER JOIN `user` as u ON ul.`user_id` = u.`id`";
		$sql .= $where;
		$result2 = $this->query($sql);
		return array($result,$result2[0]->number_of_rows);		
	}
	function getUserLogDetail($id = 0, $start = 0, $end = 15){
		
		$sql = "
			SELECT ul.`id`, ula.`activity_type`, ula.`action`, ula.`datetime`
			FROM `user_log_activities` as ula INNER JOIN `user_log` as ul ON ul.`id` = ula.`user_log_id`
			WHERE ula.`user_log_id` = '" . $id . "'
		";
			
		$sql .= " ORDER BY  `datetime` DESC LIMIT $start,$end";
		$result = $this->query($sql);
		/* For Pagination */
		$sql = "SELECT COUNT(ula.`id`) AS number_of_rows
				FROM `user_log_activities` as ula INNER JOIN `user_log` as ul ON ul.`id` = ula.`user_log_id`
				WHERE ula.`user_log_id` = '" . $id . "'
			";		
		$result2 = $this->query($sql);
		return array($result,$result2[0]->number_of_rows);
	}
	function deleteLog($id = 0){
		$sql = "
				DELETE ul,ula,gs
				FROM `user_log` as ul 
				JOIN `user_log_activities` as ula 
					ON ul.`id` = ula.`user_log_id` 
				JOIN `guest_survey` as gs 
					ON ula.`id` = gs.`user_log_activities_id` 
				WHERE ul.`id` = '" . $id . "' AND ul.`logout` <> ''
		";
		$result = $this->query($sql);
		if(count($result) == 0){
			$sql = "
				DELETE ul,ula
				FROM `user_log` as ul
				JOIN `user_log_activities` as ula
					ON ul.`id` = ula.`user_log_id`
				WHERE ul.`id` = '" . $id . "' AND ul.`logout` <> ''
					";
		}
		$result = $this->query($sql);
		if(count($result) == 0){
			$sql = "
				DELETE ul
				FROM `user_log` as ul 				
				WHERE ul.`id` = '" . $id . "' AND ul.`logout` <> ''
					";
		}
		$result = $this->query($sql);		
		return $result;
	}
	function updateEmailSent(){
		$sql = "
			UPDATE `guest_survey` SET `status` = 0
		";
		return $this->query($sql);
	}
	function checkPassword($userid = 0, $password = ""){
		$sql = "
		SELECT COUNT(`id`) as number
		FROM `user`
		WHERE `id` = '" . $userid . "' AND `password` = '" . b64encode($password) . "'	
		";
		$result = $this->query($sql);
		return $result[0]->number;
	}
	function resetPassowrd($userid = 0){
		$sql = "
		UPDATE " . $this -> GetPrefix('users') . " SET `password` = '" . b64encode("123456") . "'
		WHERE `id` = '" . $userid . "'
		";
		return $this->query($sql);
	}
	function deleteUser($userid = 0){
		$sql = "
		DELETE FROM tbl_users
		 WHERE id = " . $userid . " AND username <> 'admin'
		";
		return $this->query($sql);
	}
	function updateStatus($userid = 0, $password = ""){
		$sql = "
		UPDATE " . $this -> GetPrefix('users') . " as u 
		JOIN `user` as u2 
			ON u.`id` = u2.`id` 
		SET u.`status` = IF(u2.`status` = 1,0,1)
		WHERE u.`id` = '" . $userid . "' 
			AND u.`username` <> 'admin'
		";
		return $this->query($sql);
	}
	function updateUserInfo($data = array()){		
		return $this->update($data);
	}
	/*
	 * Group User
	 * */
	function getGroups($userid = 0,$search = ""){
		$sql = "
		SELECT g.*
		FROM " . $this -> GetPrefix('users_group') . " as g				
		";
		if($userid != 0 && $search == ""){
			$sql .= " WHERE g.`id` = '" . $userid . "'";
		}elseif($userid == 0 && $search != ""){
			$sql .= " WHERE `title` LIKE '%" . $search . "%'";
		}elseif($userid != 0 && $search != ""){
			$sql .= " WHERE g.`id` = '" . $userid . "' && `title`= '%" . $search . "%'";
		}
		$result = $this->query($sql);
		return $result;
	}
	function deleteGroup($groupid = 0){
		$sql = "
		DELETE FROM `tbl_users_group`
		WHERE `id` = '" . $groupid . "' AND `alias` <> 'admin'
		";
		return $this->query($sql);
	}
}
?>